<?php
namespace app\console\controller;
use app\model\Express;
use app\model\OrderGoods;
use app\model\WorkerRecords as SelfModel;
use app\model\Company;
use app\model\WorkerType;
use app\model\Depart;


use PhpOffice\PhpSpreadsheet\IOFactory;
use think\db\exception\DataNotFoundException;
use think\db\exception\DbException;
use think\db\exception\ModelNotFoundException;
use think\facade\Request;
use think\facade\Route;
use think\facade\View;
use think\facade\Db;
use think\facade\Cache;
use think\facade\Cookie;
class WorkerRecords extends Base
{


    /**
     * @return mixed
     * 查询
     */
    public function index()
    {

        $param = Request::param();
        $ids = session('data_id_str');
        $where = [];
        if(!empty($ids)) $where[] = ['company_id','in',$ids];
        $data = (new SelfModel())->withSearch(array_keys($param),$param)->where($where)->order(['sort'=>'asc','id'=>'desc'])->paginate(['query' => $param,'list_rows'=>100])->each(function ($item,$key){

        });
        $this->select();
        $x_data = [];
        $da = [];

        for($i=1;$i<=date('t');$i++){
            $x_data[] = $i;
            $time = date('Y-m-d',mktime(0,0,0,date('m'),$i,date('Y')));

            $da[] = \app\model\WorkerRecords::where('create_time','like','%'.$time.'%')->withSearch(array_keys($param),$param)->where($where)->sum('price');

        }
        $total_price =(new SelfModel())->withSearch(array_keys($param),$param)->where($where)->order(['sort'=>'asc','id'=>'desc'])->sum('price');
//        dump($da);

        return View::fetch('', ['data' => $data,'xdata'=>json_encode($x_data),'da'=>json_encode($da),'total_price'=>$total_price]);
    }

    /**
     * 添加
     * @return mixed
     */
    public function create()
    {

        if ($this->request->isPost()) {
            $inquire = $this->request->param();

            $ret = (new SelfModel())->save($inquire);
            if ($ret) {
                win('操作成功', Route::buildUrl('index'));
            }
            fail('操作失败');
        }
        $this->select();
        return View::fetch('', ['info' => []]);
    }

    /**
     * 修改
     * @param $id
     * @return mixed
     */
    public function update()
    {
        $param = $this->request->param();
        $info = SelfModel::find($param['id']);
        if ($this->request->isPost()) {
            $ret = $info->save($param);
            if ($ret !== false) {
                win('修改成功', Route::buildUrl('index'));
            }
            fail('修改失败');
        }
        $this->select();
        return View::fetch('create', ['info' => $info]);
    }

    public function select()
    {
            $ids = session('data_id_str');
            $where = [];
            if(!empty($ids)) $where[] = ['id','in',$ids];
            $company = Company::order(['sort' => 'asc', 'id' => 'desc'])->where($where)->select()->toArray();
            View::assign('company', $company);
            $worker_type = WorkerType::order(['sort' => 'asc', 'id' => 'desc'])->select()->toArray();
            View::assign('worker_type', $worker_type);
            $depart = Depart::order(['sort' => 'asc', 'id' => 'desc'])->select()->toArray();
            View::assign('depart', $depart);
            $worker_type = WorkerType::order(['sort' => 'asc', 'id' => 'desc'])->select()->toArray();
            View::assign('worker_type', $worker_type);

            if(input('id')){
                $company_id = \app\model\Worker::where('id',input('id'))->value('company_id');//where('company_id',$company_id)->
                $worker = \app\model\Worker::order(['sort' => 'asc', 'id' => 'desc'])->where('company_id',$company_id)->select();
            }else{
                $worker = \app\model\Worker::order(['sort' => 'asc', 'id' => 'desc'])->where('company_id',input('company_id'))->select();
            }

            foreach ($worker as &$v){
                $v['title'] = $v['name'].' '.$v['mobile'].' '.$v['depart_title'].' '.$v['worker_type_title'];
            }

        View::assign('worker', $worker);

    }

    /**
     * 删除方法
     * @param $id
     */
    public function destory($id)
    {
        if ($this->request->isGet()) {
            $ret = SelfModel::destroy($id);
            if ($ret) jump('删除成功', Route::buildUrl('index'));
            fail('删除失败');
        }
    }

    /**
     * 软删除数据恢复方法
     * @param $id
     */
    public function restore($id)
    {
        $user = SelfModel::onlyTrashed()->find($id);
        $ret = $user->restore();
        if ($ret === true) {
            jump('恢复成功', Route::buildUrl('index'));
        }
    }

    /**
     * 公共切换状态
     * @return bool|int
     */
    public function check()
    {
        $inquire = $this->request->param();
        $ret = (new SelfModel())->where('id', $inquire['id'])->value($inquire['check']) == '1' ? 2 : 1;
        if ((new SelfModel())->where('id', $inquire['id'])->update([$inquire['check'] => $ret])) {
            return $ret;
        }
        return false;
    }


    /**
     * 首页批量更新
     */
    public function index_update()
    {
        $param = $this->request->param();
        $data = [];
        if(!empty($param['sort'])){
            foreach ($param['sort'] as $k => $v) {
                $data[$k]['id'] = $k;
                $data[$k]['sort'] = $v;
                // $ret = Db::name($this->controller)->where('id',$k)->setField('sort',$v);
            }

        }
        $ret = (new SelfModel())->allowField(['id','sort'])->saveAll($data);
        if ($ret !== false) {
            jump('更新成功', Route::buildUrl('index'));
        }
    }

    public function setField()
    {
        $param = Request::param();

        $ret = SelfModel::where('id', $param['id'])->save([$param['field'] => $param['value']]);

        if ($ret) {
            return json(['code' => 200, 'message' => '修改成功']);
        }
        return json(['code' => 400, 'message' => '没有修改']);
    }

    public function export($filename='1.xlsx')
    {
        $filename = '绩效导出';
        $param = $this->request->param();
        if(!empty($param['create_time'])){
            $filename .= ' '.$param['create_time'];
        }
        $filename .= '.xlsx';
        $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle('数据');
        $header = ['ID','公司名称','所属部门','所属岗位','员工姓名','员工手机号','报单金额','创建时间'];
        foreach ($header as $kh => $vh){
            $sheet->setCellValueByColumnAndRow($kh+1,1, $vh);
        }
        $ids = session('data_id_str');
        $where = [];
        if(!empty($ids)) $where[] = ['company_id','in',$ids];
        $data = (new SelfModel())->withSearch(array_keys($param),$param)->where($where)->order(['sort'=>'asc','id'=>'desc'])->select();

        $i = 2;

        foreach ($data as $k => $v){
            $sheet->setCellValueByColumnAndRow(1, $i, $v['id']);
            $sheet->setCellValueByColumnAndRow(2, $i, $v['company_title']);
            $sheet->setCellValueByColumnAndRow(3, $i, $v['worker_depart_title']);
            $sheet->setCellValueByColumnAndRow(4, $i, $v['worker_type_title']);
            $sheet->setCellValueByColumnAndRow(5, $i, $v['worker_name']);
            $sheet->setCellValueByColumnAndRow(6, $i, $v['worker_mobile']);
            $sheet->setCellValueByColumnAndRow(7, $i, $v['price']);
            $sheet->setCellValueByColumnAndRow(8, $i, $v['create_time']);
            $i++;
//            $objValidation = $sheet->getCellByColumnAndRow(12,$k+1)->getDataValidation();
//            // $objValidation = $objActSheet->getCell("{$col}" . $i)->getDataValidation()
//            $objValidation->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST );
//            $objValidation->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
//            $objValidation->setAllowBlank(false);
//            $objValidation->setShowErrorMessage(true);
//            $objValidation->setShowDropDown(true);
//            $objValidation->setErrorTitle('Input error');
//            $objValidation->setError('Value is not in list.');
//            $objValidation->setPromptTitle('Pick from list');
//        $objValidation->setPrompt('Please pick a value from the drop-down list.');
//            $express = Express::where('status',1)->column('title');
//
//            $express = implode(',',$express);
//
//            $objValidation->setFormula1('"'.$express.'"');



        }

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$filename.'"');
        header('Cache-Control: max-age=0');
        // If you're serving to IE 9, then the following may be needed
        header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        header('Pragma: public'); // HTTP/1.0

        $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        ob_end_flush();
        ob_end_clean();//清除缓冲区,避免乱码


    }

     public function write($data = [], $filename = '订单导出', $set_title = '表格1')
    {
        //$filename =  '订单导出';
        //$set_title = '订单导出';
        require_once root_path() . 'extend/phpoffice/phpexcel/Classes/PHPExcel.php';
        $objPHPExcel = new \PHPExcel();
        ini_set("memory_limit", "1024M"); // 设置php可使用内存
        set_time_limit(0);
        error_reporting(E_ALL);
        date_default_timezone_set('Asia/Shanghai');
        $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_gzip;
        if (!\PHPExcel_Settings::setCacheStorageMethod($cacheMethod)) {
            die($cacheMethod . " 缓存方法不可用" . EOL);
        }
        $objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
            ->setLastModifiedBy("Maarten Balliauw")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Test result file");

        /*----------------------- 设置导出标题------------------------*/
        $objPHPExcel->setActiveSheetIndex(0);
        $az = [
            'A' => 30,
            'B' => 20,
            'C' => 20,
            'D' => 20,
            'E' => 20,
            'F' => 20,
            'G' => 20,
            'H' => 20,
            'I' => 30,
            'J' => 20,
            'K' => 20,
            'L' => 20,
            'M' => 20,
            'N' => 20,
            'O' => 20,
            'P' => 20,
            'Q' => 20,
            'R' => 20,
            'S' => 20,
            'T' => 20,
            'U' => 20,
            'V' => 20,
            'W' => 20,
            'X' => 20,
            'Y' => 20,
            'Z' => 20
        ];
        $az2 = array_keys($az);//给data数组循环使用
        $th = [
            '订单号' => 'order_number',
            '姓名' => 'name',
            '手机号' => 'mobile',
            '主管' => 'zhuguan',
            '教官' => 'jiaoguan',
            '订单价格' => 'order_price',
            '配送方式' => 'distribution_type',
            '省市区' => 'pca',
            '地址' => 'address',
            '状态' => 'status_text',
            '创建时间' => 'create_time'
        ];
        $th2 = array_keys($th);//
        $th3 = array_values($th);//索引数组转关联数组
        $az_i = 0;

        foreach ($az as $az_k => $az_v) {
            //设置第一列导出标题
            if (!empty($th2[$az_i])) {
                $objPHPExcel->getActiveSheet()->setCellValue($az_k . '1', $th2[$az_i]);
                //设置单元列的宽度
                $objPHPExcel->getActiveSheet()->getColumnDimension($az_k)->setWidth($az_v);
                $az_i++;
            }

        }

        foreach ($data as $k => $v) {
            $i = $k + 2; //循环列 A2 A3 A4
            $k2_i = 0;//循环行 A2 B2 C3

            foreach ($th3 as $ava) {
                $objPHPExcel->getActiveSheet()->setCellValue($az2[$k2_i] . ($i), "\t" . $v[$ava]);//循环行
                $k2_i++;
            }
        }


        $objPHPExcel->getActiveSheet()->setTitle($set_title);
        echo date('H:i:s'), " 设置格式为Excel2007版格式", EOL;
        $callStartTime = microtime(true);

        //$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        //$objWriter->save(str_replace('.php', '.xlsx', __FILE__));

        ob_end_clean();
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;charset=utf-8;filename="' . $filename . '.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');

        $callEndTime = microtime(true);
        $callTime = $callEndTime - $callStartTime;

        echo date('H:i:s'), " 设置生成的文件为： ", str_replace('.php', '.xlsx', pathinfo(__FILE__, PATHINFO_BASENAME)), EOL;
        echo date('H:i:s'), ' 写入Workbook中耗时 ', sprintf('%.4f', $callTime), " 秒", EOL;
        echo date('H:i:s'), ' 当前内存使用情况: ', (memory_get_usage(true) / 1024 / 1024), " MB", EOL;
        echo date('H:i:s'), " 内存使用峰值: ", (memory_get_peak_usage(true) / 1024 / 1024), " MB", EOL;
        echo date('H:i:s'), " 完成写入文件", EOL;
        echo date('H:i:s'), ' 文件被创建在： ', getcwd(), '目录', EOL;
    }








}